package com.a1573595.musicplayer.dbHelper

import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import com.a1573595.musicplayer.model.City

class CityDatabaseHelper(context: Context) : SQLiteOpenHelper(context, DB_NAME, null, DB_VERSION) {

    companion object {
        private const val DB_NAME = "city_database.db"
        private const val DB_VERSION = 1
        private const val TABLE_NAME = "cities"
        private const val CREATE_TABLE = """
            CREATE TABLE $TABLE_NAME (
                id INTEGER PRIMARY KEY,
                pid INTEGER,
                city_code TEXT,
                city_name TEXT NOT NULL,
                post_code TEXT,
                area_code TEXT,
                ctime TEXT
            )
        """
    }

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(CREATE_TABLE)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS $TABLE_NAME")
        onCreate(db)
    }

    fun insertCity(city: City) {
        val db = writableDatabase
        val values = ContentValues().apply {
            put("id", city.id)
            put("pid", city.pid)
            put("city_code", city.cityCode)
            put("city_name", city.cityName)
            put("post_code", city.postCode)
            put("area_code", city.areaCode)
            put("ctime", city.ctime)
        }
        db.insert(TABLE_NAME, null, values)
        db.close()
    }

    fun findCityByName(cityName: String): City? {
        val db = readableDatabase
        val cursor = db.query(
            TABLE_NAME,
            null,
            "city_name = ?",
            arrayOf(cityName),
            null,
            null,
            null
        )
        var city: City? = null
        if (cursor.moveToFirst()) {
            city = City(
                id = cursor.getInt(cursor.getColumnIndexOrThrow("id")),
                pid = cursor.getInt(cursor.getColumnIndexOrThrow("pid")),
                cityCode = cursor.getString(cursor.getColumnIndexOrThrow("city_code")),
                cityName = cursor.getString(cursor.getColumnIndexOrThrow("city_name")),
                postCode = cursor.getString(cursor.getColumnIndexOrThrow("post_code")),
                areaCode = cursor.getString(cursor.getColumnIndexOrThrow("area_code")),
                ctime = cursor.getString(cursor.getColumnIndexOrThrow("ctime"))
            )
        }
        cursor.close()
        db.close()
        return city
    }
}
